package cos480.amiwsd;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/**
 * DBMgmt:
 * 
 * The general database management class, checks for existing tables and creates those which aren't there.
 * 
 * @author will, emma
 *
 */

public class DBMgmt {

	
	
	public Connection connect(String[] userpass) throws SQLException {
		return DriverManager.getConnection("jdbc:postgresql" +
				//"://suzhou.umemat.maine.edu/" + "amiwsd", userpass[0], userpass[1]);
				"://localhost/" + userpass[0], userpass[0], userpass[1]);
	}
	
	public boolean tableCheck(Statement sql, String name) {
		
		//default, returns false.
		boolean ret = false;
		
		try {
			ResultSet rs = sql.executeQuery("select tablename from pg_tables where tablename = \'" + name + "\'");
			if (rs.next())
				ret = true;
		} catch (SQLException e) {
			System.err.println("Failed to check for the table named '" + name + "': " + e.getMessage());
			e.printStackTrace();
		}
		
		return ret;
	}
	
	
	
	
	public void createLandscape(Statement sql) {
		
		try {
			sql.execute("create table landscape (land_id serial primary key, intervals double precision[][])");
		} catch (Exception e) {
			System.err.println("Failed to create the landscape table.");
			e.printStackTrace();
		}
		
	}
	
	public void createInstance(Statement sql) {
		try {
			sql.execute("create table instance (inst_id serial primary key, land_id integer, proportions double precision[])");
		} catch (Exception e) {
			System.err.println("Failed to create the instance table.");
			e.printStackTrace();
		}
	}
	
	
	/**
	 * createSimTable:
	 *  
	 * Creates the simulation-related tables with the following schemas:
	 * 
	 * simulation(s_id primary key, p_0, p_1, p_2, \phi, \mu, \gamma, type(=SIR/SIRS/SIS), instance_id)
	 * 
	 * SimResult(s_id foreign key, timestamp primary key, IGR, I, ...)
	 * 
	 * @param sql Statement object
	 */
	
	public void createSimTable(Statement sql) {
		try {
			sql.executeUpdate("create table simulation (s_id serial, p_0 double precision, p_1 double precision, " +
					"p_2 double precision, phi double precision, mu double precision, gamma double precision, type varchar(5), " +
					"instance_id integer, numSims integer, minTime integer, maxTime integer, I0 integer, CRnum integer, filename text," +
					"primary key (p_0, p_1, p_2, phi, mu, gamma, type, instance_id))");
			sql.executeUpdate("alter table simulation add constraint SimTypeCheck check ( type = 'SIR' or type = 'SIS' or type = 'SIRS')");
		} catch (SQLException e) {
			System.err.println("Failed to create the simulation table: " + e.getMessage());
			e.printStackTrace();
			return;
		}
	
		try {
			sql.executeUpdate("create table simresult (sr_id serial, s_id integer, ts timestamp, qn double precision, " +
					"qhh double precision, peak_i_qn double precision, peak_i_qhh double precision, igr double precision, " +
					"peak_i double precision, final_i_avg double precision, ncount integer, peak_in_count integer, filename text," +
					"primary key (sr_id))");
		} catch (SQLException e) {
			System.err.println("Failed to create the simresult table: " + e.getMessage());
			e.printStackTrace();
		}
		
	}
	
	
	/**
	 * createScanTable:
	 * 
	 * creates the scan table with the following schema:
	 * 
	 * scan(scan_date primary key, size)
	 * 
	 * @param sql Statement object
	 */
	
	public void createScanTable(Statement sql) {
		
		try {
			sql.executeUpdate("create table scan(scan_id serial, scan_date date, success char(1), primary key (scan_date, success))");
		} catch (Exception e) {
			System.err.println("Failed to create the scan table: " + e.getMessage());
			e.printStackTrace();
		}
		
	}
	
	
	/**
	 * createHostTable:
	 * 
	 * creates the host table with the following schema: 
	 * 
	 * host(ip primary key, allocation)
	 * 
	 * @param con Connection object
	 */
	
	public void createHostTable(Statement sql) {
		
		try {
			// TODO "new"
			//sql.executeUpdate("create table host_new(ip inet primary key, allocation varchar(64) default null)");
			sql.executeUpdate("create table host(ip inet primary key, allocation varchar(64) default null)");
			
			
			// create function & trigger to ensure only one entry per ip
			//sql.executeUpdate("create function dupe_host_check() returns trigger as $host$ " + 
			//				  	"begin if (new.ip in (select ip from old)) then return old; end if; end;");
			//sql.executeUpdate("create trigger dupe_host_check before insert on host for each row execute procedure dupe_host_check();");
		} catch (SQLException e) {
			System.err.println("Failed to create host table: " + e.getMessage());
			e.printStackTrace();
		}
		
	}
	
	/**
	 * Creates the ResultOfScan table with the following schema:
	 * 
	 * resultofscan(scan_date primary key, ip foreign key host(ip), result_type(=s,f,e), result)
	 * 
	 * @param con Connection object
	 */
	public void createResultOfScanTable(Statement sql){
		try{
			// TODO "new"
			//sql.executeUpdate("create table resultofscan_new(ip inet, result text default null, scan_id integer, primary key(ip, scan_id))");
			sql.executeUpdate("create table resultofscan(ip inet, result text default null, scan_id integer, primary key(ip, scan_id))");
			
			
			//sql.executeUpdate("alter table resultofscan add constraint check ip in ");
		} catch (SQLException e){
			e.printStackTrace();
		}
	}

	public void createPSSTDefinitionTable(Statement sql) {
		try{
			sql.executeUpdate("create table psstdefinition(name text primary key, statement text)");
		} catch (SQLException e){
			e.printStackTrace();
		}
	}
	
}
